#!/usr/bin/env python
# -*- coding:utf-8 -*-
# Author:Zhangcl
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, ForeignKey, UniqueConstraint, Index, DateTime, update, func
from sqlalchemy.orm import sessionmaker, relationship, mapper
from sqlalchemy import and_, or_
from conf.setting import db_connect_info
engine = create_engine('mysql+pymysql://%s:%s@%s/%s?charset=utf8' % (db_connect_info['user'],
                                                                     db_connect_info['passwd'],
                                                                     db_connect_info['host'],
                                                                     db_connect_info['db_name']))
# create database machinedb charset "utf8"; #创建数据库
Base = declarative_base()

# 主机与主机组关联表
BindHost_Group = Table('bindhost_group', Base.metadata,
                       Column('bindhost_id', ForeignKey('bind_host.id'), primary_key=True),
                       Column('group_id', ForeignKey('groups.id'), primary_key=True))

# 堡垒机用户与主机组关联表
Group_UserProfile = Table('group_userprofile', Base.metadata,
                          Column('userprofile_id', ForeignKey('user_profile.id'), primary_key=True),
                          Column('group_id', ForeignKey('groups.id'), primary_key=True))


class Group_with_UserProfile(object):
    def __init__(self, userprofile_id, group_id):
        self.userprofile_id = userprofile_id
        self.group_id = group_id
mapper(Group_with_UserProfile, Group_UserProfile)


class BindHost_with_Group(object):
    def __init__(self, bindhost_id, group_id):
        self.bindhost_id = bindhost_id
        self.group_id = group_id
mapper(BindHost_with_Group, BindHost_Group)


class UserProfile(Base):
    '''
    堡垒机用户表
    '''
    __tablename__ = 'user_profile'

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(32), unique=True, nullable=False)
    password = Column(String(128), nullable=False)
    groups = relationship('Group', secondary=Group_UserProfile)
    audit_logs = relationship('AuditLog')

    def __repr__(self):
        return '%s %s %s' % (self.id, self.username, self.password)


class RemoteUser(Base):
    '''
    远程服务器验证账户密码
    '''
    __tablename__ = 'remote_user'

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(64), nullable=False)
    password = Column(String(255))

    __table_args__ = (UniqueConstraint('username', 'password', name='_user_passwd'),)

    def __repr__(self):
        return '%s %s' % (self.id, self.username)


class Host(Base):
    '''
    主机表
    '''
    __tablename__ = 'host'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    ip_addr = Column(String(128), unique=True, nullable=False)
    port = Column(Integer, default=22)

    def __repr__(self):
        return '%s %s %s %s' % (self.id, self.hostname, self.ip_addr, self.port)


class Group(Base):
    '''
    主机组表
    '''
    __tablename__ = 'groups'

    id = Column(Integer, primary_key=True)
    name = Column(String(64), nullable=False, unique=True)
    bind_hosts = relationship("BindHost", secondary=BindHost_Group, back_populates='groups')
    user_profiles = relationship("UserProfile", secondary=Group_UserProfile)

    def __repr__(self):
        return '%s %s' % (self.id, self.name)


class BindHost(Base):
    '''
    主机绑定表
    '''
    __tablename__ = 'bind_host'

    id = Column(Integer, primary_key=True, autoincrement=True)
    host_id = Column(Integer, ForeignKey('host.id'))
    remoteuser_id = Column(Integer, ForeignKey('remote_user.id'))
    host = relationship("Host")
    remoteuser = relationship("RemoteUser")
    groups = relationship("Group", secondary=BindHost_Group, back_populates='bind_hosts')
    audit_logs = relationship('AuditLog')

    __table_args__ = (UniqueConstraint('host_id', 'remoteuser_id', name='bindhost_and_user'),)

    def __repr__(self):
        return '%s %s %s %s %s %s %s' % \
               (self.id, self.host_id, self.host.ip_addr, self.remoteuser_id,
                self.remoteuser.username, self.remoteuser.password, self.groups.id)


class AuditLog(Base):
    '''
    操作日志表
    '''
    __tablename__ = 'audit_log'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user_profile.id'))
    bind_host_id = Column(Integer, ForeignKey('bind_host.id'))
    cmd = Column(String(255))
    date = Column(DateTime)
    user_profile = relationship("UserProfile")
    bind_host = relationship("BindHost")

    def __repr__(self):
        return '%s %s %s %s %s' % (self.id, self.user_profile.username, self.bind_host_id, self.cmd, self.date)


class Admin_user(Base):
    '''
    管理员账户表
    '''
    __tablename__ = 'admin_user'

    id = Column(Integer, primary_key=True)
    admin_name = Column(String(64), nullable=False, unique=True)
    password = Column(String(255))

    def __repr__(self):
        return '%s %s %s' % (self.id, self.admin_name, self.password)


Base.metadata.create_all(engine)  # 创建表
# session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
# session = session_class()  # 生成session实例
# a1 = Admin_user(admin_name='admin', password='123') #创建管理员账户密码
# session.add(a1)
# session.commit()



